import tkinter as tk
import pymysql
import tkinter.messagebox
class App():
    def __init__(self,root):
        root.title('Management SQL')
        root.geometry('600x600')

        tk.Label(root, text='NANE').place(x=250, y=40)
        tk.Label(root, text='AGR').place(x=385, y=40)
        tk.Label(root, text='GENDER').place(x=500, y=40)

        self.var_I_1 = tk.StringVar()
        self.var_I_2 = tk.StringVar()
        self.var_I_3 = tk.StringVar()
        self.var_D_1 = tk.StringVar()
        self.var_D_2 = tk.StringVar()
        self.var_D_3 = tk.StringVar()
        Increase1 = tk.Entry(root, textvariable=self.var_I_1, width=10)
        Increase1.place(x=235, y=105)
        Increase2 = tk.Entry(root, textvariable=self.var_I_2, width=10)
        Increase2.place(x=365, y=105)
        Increase3 = tk.Entry(root, textvariable=self.var_I_3, width=10)
        Increase3.place(x=495, y=105)

        Increase = tk.Button(root, text='Increase record', width=15, command=self.Increase_record)
        Increase.place(x=30, y=100)

        Delete1 = tk.Entry(root, textvariable=self.var_D_1, width=10)
        Delete1.place(x=235, y=235)
        Delete2 = tk.Entry(root, textvariable=self.var_D_2, width=10)
        Delete2.place(x=365, y=235)
        Delete3 = tk.Entry(root, textvariable=self.var_D_3, width=10)
        Delete3.place(x=495, y=235)
        Delete = tk.Button(root, text='Delete record', width=15, command=self.Delete_record)
        Delete.place(x=30, y=230)

        self.var_S_1 = tk.StringVar()
        self.var_S_2 = tk.StringVar()
        self.var_S_3 = tk.StringVar()
        Search1 = tk.Entry(root, textvariable=self.var_S_1, width=10)
        Search1.place(x=235, y=365)
        Search2 = tk.Entry(root, textvariable=self.var_S_2, width=10)
        Search2.place(x=365, y=365)
        Search3 = tk.Entry(root, textvariable=self.var_S_3, width=10)
        Search3.place(x=495, y=365)
        Search = tk.Button(root, text='Search record', width=15, command=self.Search_record)
        Search.place(x=30, y=360)

        self.var_M_1 = tk.StringVar()
        self.var_M_2 = tk.StringVar()
        self.var_M_3 = tk.StringVar()
        self.Modify1 = tk.Entry(root, textvariable=self.var_M_1, width=10)
        self.var_M_4 = tk.StringVar()
        self.var_M_5 = tk.StringVar()
        self.var_M_6 = tk.StringVar()
        self.e_1 = tk.StringVar()
        self.e_2 = tk.StringVar()
        self.e_3 = tk.StringVar()
        Modify1 = tk.Entry(root, textvariable=self.var_M_1, width=10)
        Modify1.place(x=235, y=495)
        Modify2 = tk.Entry(root, textvariable=self.var_M_2, width=10)
        Modify2.place(x=365, y=495)
        Modify3 = tk.Entry(root, textvariable=self.var_M_3, width=10)
        Modify3.place(x=495, y=495)
        Modify = tk.Button(root, text='Modify record', width=15, command=self.Modify_record)
        Modify.place(x=30, y=490)

    def Increase_record(self):
        var_I_11 = self.var_I_1.get()
        var_I_22 = self.var_I_2.get()
        var_I_33 = self.var_I_3.get()
        if var_I_22.isdigit():
            if var_I_33 == '男' or var_I_33 == '女':
                a = app.executeq("INSERT INTO test(name,age,gender)VALUES (%s,%s,%s)",(var_I_11, var_I_22, var_I_33))
                if a:
                    tk.messagebox.showinfo(message='添加成功')
                else:
                    tk.messagebox.showerror(message='重复记录')
            else:
                tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
        else:
            tk.messagebox.showerror(title='无效数据', message='age请输入数字')

    def Delete_record(self):
        var_D_11 = self.var_D_1.get()
        var_D_22 = self.var_D_2.get()
        var_D_33 = self.var_D_3.get()
        if var_D_11:
            if var_D_22 and var_D_33:
                a = app.query("SELECT name  FROM test WHERE NAME=(%s) and (age=(%s) and gender=(%s))", (var_D_11,var_D_22, var_D_33))
                if a:
                    b = app.executeq("DELETE FROM test WHERE NAME=(%s) and (age=(%s) and gender=(%s))", (var_D_11,var_D_22, var_D_33))
                    if b:
                        tk.messagebox.showinfo(message='删除成功')
                else:
                    tk.messagebox.showerror(message='无记录')

        elif var_D_22:
            if var_D_33:
                a = app.query("SELECT name  FROM test WHERE age=(%s) and gender=(%s)", (var_D_22, var_D_33))
                if a:
                    b = app.executeq("DELETE FROM test WHERE age=(%s) and gender=(%s)", (var_D_22, var_D_33))
                    if b:
                        tk.messagebox.showinfo(message='删除成功')
                else:
                    tk.messagebox.showerror(message='无记录')
            else:
                a = app.query("SELECT name  FROM test WHERE age=(%s)", (var_D_22))
                if a:
                    b = app.executeq("DELETE FROM test WHERE age=(%s)", (var_D_22))
                    if b:
                        tk.messagebox.showinfo(message='删除成功')
                else:
                    tk.messagebox.showerror(message='无记录')

        else:
            a = app.query("SELECT name  FROM test WHERE gender=(%s)", (var_D_33))
            if a:
                b = app.executeq("DELETE FROM test WHERE gender=(%s)", (var_D_33))
                if b:
                    tk.messagebox.showinfo(message='删除成功')
            else:
                tk.messagebox.showerror(message='无记录')

    def Search_record(self):
        var_S_11 = self.var_S_1.get()
        var_S_22 = self.var_S_2.get()
        var_S_33 = self.var_S_3.get()
        if var_S_11:
            if var_S_22:
                if var_S_33:
                    a = app.query("SELECT name,age,gender FROM test WHERE name like (%s) and age=(%s) and gender = (%s)",(var_S_11 + '%', var_S_22, var_S_33))
                    if a:
                        app.root_2(a)
                    else:
                        tk.messagebox.showerror(message='无记录')
                else:
                    a = app.query(
                        "SELECT name,age,gender FROM test WHERE name like (%s) and age=(%s)",(var_S_11 + '%', var_S_22))
                    if a:
                        app.root_2(a)
                    else:
                        tk.messagebox.showerror(message='无记录')
            else:
                a = app.query("SELECT name,age,gender FROM test WHERE name like (%s)", (var_S_11 + '%'))
                if a:
                    app.root_2(a)
                else:
                    tk.messagebox.showerror(message='无记录')
        elif var_S_22:
                if var_S_33:
                    a = app.query("SELECT name,age,gender FROM test WHERE age=(%s) and gender = (%s)", (var_S_22,var_S_33))
                    if a:
                        app.root_2(a)
                    else:
                        tk.messagebox.showerror(message='无记录')
                else:
                    a = app.query("SELECT name,age,gender FROM test WHERE age=(%s)",(var_S_22))
                    if a:
                        app.root_2(a)
                    else:
                        tk.messagebox.showerror(message='无记录')

        else:
            a = app.query("SELECT name,age,gender FROM test WHERE gender = (%s)", (var_S_33))
            if a:
                app.root_2(a)
            else:
                tk.messagebox.showerror(message='无记录')

    def Modify_record(self):
        var_M_11 = self.var_M_1.get()
        var_M_22 = self.var_M_2.get()
        var_M_33 = self.var_M_3.get()
        if var_M_11:
            if var_M_22:
                if var_M_33:
                    a = app.query("SELECT name,age,gender FROM test WHERE name=(%s) and age=(%s) and gender = (%s)",(var_M_11 , var_M_22, var_M_33))
                    if a:
                        app.root_3(var_M_11,var_M_22,var_M_33)
                    else:
                        tk.messagebox.showerror(message='无记录')
                else:
                    tk.messagebox.showerror(message='GENDER 输入 ‘男’ 或 ‘女’ ')
            else:
                tk.messagebox.showerror(message='AGE 请输入整数 ')
        else:
            tk.messagebox.showerror(message='请输入名字')

    def Modify_record_1(self):
        var_M_66 = self.var_M_1.get()
        var_e_1 = self.e_1.get()
        var_e_2 = self.e_2.get()
        var_e_3 = self.e_3.get()
        mess = tk.messagebox.askyesno(title='are you sure', message='确定将' + '  ' + var_M_66 + '  ' + '进行修改吗？')
        if mess:
            if var_e_1 == var_M_66:
                if var_e_2.isdigit():
                    if var_e_3 == '男' or var_e_3 == '女':
                        app.executeq("UPDATE test SET name = (%s), age = (%s) ,gender = (%s) WHERE  name = (%s)",
                                     (var_e_1, var_e_2, var_e_3, var_e_1))
                        tk.messagebox.showinfo(message='修改成功')
                    else:
                        tk.messagebox.showerror(message='GENDER 输入 ‘男’ 或 ‘女’ ')
                else:
                    tk.messagebox.showerror(message='AGE 请输入整数 ')
            else:
                tk.messagebox.showerror(message='名字无法修改')

    def root_2(self,a):
        root_2 = tk.Toplevel(root)
        root_2.geometry('300x300')
        tk.Label(root_2, text='NAME:').place(x=40, y=20)
        tk.Label(root_2, text='AGE:').place(x=130, y=20)
        tk.Label(root_2, text='GENDER:').place(x=200, y=20)
        s_1 = tk.Scrollbar(root_2)
        s_1.pack(side=tk.RIGHT, fill=tk.Y)

        listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
        for i in a:
            listbox_1.insert('end', str(i))

        listbox_1.place(x=40, y=60, width=220, height=230)
        s_1.config(command=listbox_1.yview)

    def root_3(self,var_M_11,var_M_22,var_M_33):
        root_3 = tk.Toplevel(root)
        root_3.geometry('300x300')
        tk.Label(root_3, text='NAME:').place(x=40, y=20)
        tk.Label(root_3, text='AGE:').place(x=130, y=20)
        tk.Label(root_3, text='GENDER:').place(x=200, y=20)
        tk.Label(root_3, text='修改为:').place(x=40, y=100)

        self.var_M_4.set(var_M_11)
        self.var_M_5.set(var_M_22)
        self.var_M_6.set(var_M_33)
        listbox_2 = tk.Listbox(root_3, listvariable=self.var_M_4)
        listbox_2.place(x=40, y=40, width=70, height=20)
        listbox_2 = tk.Listbox(root_3, listvariable=self.var_M_5)
        listbox_2.place(x=130, y=40, width=60, height=20)
        listbox_2 = tk.Listbox(root_3, listvariable=self.var_M_6)
        listbox_2.place(x=205, y=40, width=70, height=20)

        e_1 = tk.Entry(root_3, textvariable=self.e_1)
        e_1.place(x=40, y=130, width=70, height=20)
        e_2 = tk.Entry(root_3, textvariable=self.e_2)
        e_2.place(x=130, y=130, width=60, height=20)
        e_3 = tk.Entry(root_3, textvariable=self.e_3)
        e_3.place(x=205, y=130, width=70, height=20)

        tk.Button(root_3, text='修改', command=self.Modify_record_1).place(x=205, y=170, width=70, heigh=30)

    def query(self, sql_cmd, params):
        conn = pymysql.connect(
            host='localhost',
            user='root',
            passwd='123456',
            db='db_library',
            port=3306,
            charset='utf8'
        )
        cur = conn.cursor()
        try:
            cur.execute(sql_cmd, params)
            ret = cur.fetchall()
            if params:
                cur.execute(sql_cmd, params)
            else:
                cur.execute(sql_cmd)
            ret = cur.fetchall()
        except Exception as e:
            print(e)
        finally:
            if cur:
                cur.close()
            if conn:
                conn.close()
        return ret

    def executeq(self, sql_cmd, params):
        ret = True
        conn = pymysql.connect(
            host='localhost',
            user='root',
            passwd='123456',
            db='db_library',
            port=3306,
            charset='utf8'
        )
        cur = conn.cursor()
        try:
            if params:
                cur.execute(sql_cmd, params)
            else:
                cur.execute(sql_cmd)
            conn.commit()
        except Exception as e:
            print(e)
            ret = False
        finally:
            cur.close()
            conn.close()
        return ret

if __name__ == '__main__':
    root = tk.Tk()
    app = App(root)
    root.mainloop()